﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

/// <summary>
/// 文件名：ClsPayOutFundDAL.cs
/// 作  者：Kidy
/// 时  间：2007.01.08
/// 说  明：ClsPayOutFundDAL类用于开票信息相关处理
/// </summary>
public class ClsPayOutFundDAL
{
    /// <summary>
    /// 数据库连接字符串
    /// </summary>
    private static string strSqlConnectionString = ConfigurationManager.AppSettings["SQLCONNECTIONSTRING"];

    /// <summary>
    /// 获取所有主表信息
    /// </summary>
    /// <param name="Type">类型</param>
    /// <returns>返回主表信息数据集</returns>
    public DataSet GetPayOutFundMains(string sCurUserid,string sType)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetPayOrBackFundMains", con);
        dat.SelectCommand.CommandType = CommandType.StoredProcedure;

        dat.SelectCommand.Parameters.Add("@u_log_id", SqlDbType.NVarChar, 30);
        dat.SelectCommand.Parameters["@u_log_id"].Value = sCurUserid;
        dat.SelectCommand.Parameters.Add("@type", SqlDbType.NVarChar, 2);
        dat.SelectCommand.Parameters["@type"].Value = sType;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 获取采购付款单或采购退款单从表信息CREATE BY KIDY ON 20070108
    /// </summary>
    /// <returns>返回从表信息数据集</returns>
    public DataSet GetPayOutFundChilds(string sType,string sPay_no)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetStockPayFundChilds", con);
        dat.SelectCommand.CommandType = CommandType.StoredProcedure;

        dat.SelectCommand.Parameters.Add("@type", SqlDbType.NVarChar, 2);
        dat.SelectCommand.Parameters["@type"].Value = sType;
        dat.SelectCommand.Parameters.Add("@pay_no", SqlDbType.NVarChar, 30);
        dat.SelectCommand.Parameters["@pay_no"].Value = sPay_no;
       
        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 确认采购付款单
    /// </summary>
    /// <returns>操作是否成功</returns>
    public Boolean AuditStockPayOutFund(string sPay_No, int State, string Auditor, string Type)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AuditStockPayOutFund", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@pay_no", SqlDbType.NVarChar, 30);
        com.Parameters["@pay_no"].Value = sPay_No;
        com.Parameters.Add("@is_audit", SqlDbType.Int, 2);
        com.Parameters["@is_audit"].Value = State;
        com.Parameters.Add("@auditor", SqlDbType.NVarChar, 30);
        com.Parameters["@auditor"].Value = Auditor;
        com.Parameters.Add("@type", SqlDbType.Int, 2);
        com.Parameters["@type"].Value = Type;

        try
        {
            con.Open();
            com.ExecuteNonQuery();
            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 改变订单从表的状态
    /// </summary>
    /// <param name="Dm_No">订单单号</param>
    /// <param name="N_No">从表单号</param>
    /// <param name="State">操作状态(0确认，1取消确认即终止，2删除即作废，3彻底删除)</param>
    /// <param name="Type">类型</param>
    /// <returns>操作是否成功</returns>
    public Boolean UpdatePayOutFundChildState(string Dm_No, string N_No, int State, string Type)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpdatePayOutFundChildState", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@dm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@dm_no"].Value = Dm_No;
        com.Parameters.Add("@n_no", SqlDbType.NVarChar, 30);
        com.Parameters["@n_no"].Value = N_No;
        com.Parameters.Add("@dm_state", SqlDbType.Int, 4);
        com.Parameters["@dm_state"].Value = State;
        com.Parameters.Add("@type", SqlDbType.NVarChar, 2);
        com.Parameters["@type"].Value = Type;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 新增 采购付款 主表记录
    /// </summary>CREATE BY KIDY ON 20070108    
    /// strType, strPay_no, dPayDate, strProvide, strDept, strWriter,dOper_Date, strRemark
    public Boolean AddPayOutFund(string sType, string sPay_no, DateTime dPayDate, string sProvide, string sDept,
        string sWorker, string sBalanceType, string sWriter, DateTime dOper_Date, string sRemark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AddPayOutFund", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@type", SqlDbType.NVarChar, 2);
        com.Parameters["@type"].Value = sType;
        com.Parameters.Add("@pay_no", SqlDbType.NVarChar, 30);
        com.Parameters["@pay_no"].Value = sPay_no;
        com.Parameters.Add("@pay_date", SqlDbType.DateTime, 8);
        com.Parameters["@pay_date"].Value = dPayDate;
        com.Parameters.Add("@provide_id", SqlDbType.NVarChar, 30);
        com.Parameters["@provide_id"].Value = sProvide;
        com.Parameters.Add("@dept_id", SqlDbType.NVarChar, 30);
        com.Parameters["@dept_id"].Value = sDept;
        com.Parameters.Add("@worker", SqlDbType.NVarChar, 30);
        com.Parameters["@worker"].Value = sWorker;
        com.Parameters.Add("@BalanceType", SqlDbType.NVarChar, 30);
        com.Parameters["@BalanceType"].Value = sBalanceType;
        com.Parameters.Add("@oper", SqlDbType.NVarChar, 30);
        com.Parameters["@oper"].Value = sWriter;
        com.Parameters.Add("@oper_date", SqlDbType.DateTime, 8);
        com.Parameters["@oper_date"].Value = dOper_Date;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = sRemark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();
            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 新增从表记录
    /// </summary>
    /// <returns>返回操作是否成功</returns>
    /// strType, ctbRsPay_no.Text.Trim(), DateTime.Parse(ctsbRsPay_date.Text.Trim()),
    ///ctsbStoInvoNo.Text.Trim(),DateTime.Parse(ctbInvoDate.Text.Trim()),ctbRemark.Text.Trim(),0
    public Boolean AddPayOutFundChild(string sType, string sPay_no, DateTime dPay_date, string sVm_no, DateTime dVm_Date, 
        string sRemark, int ActionType)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AddStockPayFund", con);//Pro_AddStockPayFundChild要换成这个（还要等应付期初）
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@type", SqlDbType.NVarChar, 2);
        com.Parameters["@type"].Value = sType;
        com.Parameters.Add("@pay_no", SqlDbType.NVarChar, 30);
        com.Parameters["@pay_no"].Value = sPay_no;
        com.Parameters.Add("@pay_date", SqlDbType.DateTime, 8);
        com.Parameters["@pay_date"].Value = dPay_date;
        com.Parameters.Add("@vm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@vm_no"].Value = sVm_no;
        com.Parameters.Add("@vm_Date", SqlDbType.DateTime, 8);
        com.Parameters["@vm_Date"].Value = dVm_Date;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = sRemark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();
            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 修改采购付款单记录
    /// </summary>
    /// <returns>返回操作是否成功</returns>
    public Boolean UpdatePayOutFundM(string sType, string sPay_no, DateTime dPayDate,string sRemark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpdateStockPayFundM", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@type", SqlDbType.NVarChar, 2);
        com.Parameters["@type"].Value = sType;
        com.Parameters.Add("@pay_no", SqlDbType.NVarChar, 30);
        com.Parameters["@pay_no"].Value = sPay_no;
        com.Parameters.Add("@payDate", SqlDbType.DateTime, 8);
        com.Parameters["@payDate"].Value = dPayDate;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = sRemark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();
            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 修改从表记录
    /// </summary>
    /// <returns>返回操作是否成功</returns>
    public Boolean UpdatePayOutFundChild(string sType, string sPay_no, string sVm_no,string sIm_no,
        decimal dCurPay_money,decimal dSpareNopay,string sRemark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpdateStockPayFund", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@type", SqlDbType.NVarChar, 2);
        com.Parameters["@type"].Value = sType;
        com.Parameters.Add("@pay_no", SqlDbType.NVarChar, 30);
        com.Parameters["@pay_no"].Value = sPay_no;
        com.Parameters.Add("@vm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@vm_no"].Value = sVm_no;
        com.Parameters.Add("@Im_no", SqlDbType.NVarChar, 30);
        com.Parameters["@Im_no"].Value = sIm_no;
        com.Parameters.Add("@CurPay_money", SqlDbType.Decimal, 28);
        com.Parameters["@CurPay_money"].Value = dCurPay_money;
        com.Parameters.Add("@SpareNopay", SqlDbType.Decimal, 28);
        com.Parameters["@SpareNopay"].Value = dSpareNopay;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = sRemark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();
            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 删除采购付款单主表或者明细表记录 
    /// </summary>
    /// <param>CREATE BY KIDY ON 20070108</param>
    /// <param name="Is_Visible"></param>
    /// <returns>返回删除是否成功</returns>
    public Boolean DeleteStockPayFund(string sType, string sPay_no, string sVm_no, string sIm_no,decimal dCurPay_money, int iState)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_DeleteStockPayFund", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@type", SqlDbType.NVarChar, 30);
        com.Parameters["@type"].Value = sType;
        com.Parameters.Add("@pay_no", SqlDbType.NVarChar, 30);
        com.Parameters["@pay_no"].Value = sPay_no;
        com.Parameters.Add("@vm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@vm_no"].Value = sVm_no;
        com.Parameters.Add("@Im_no", SqlDbType.NVarChar, 30);
        com.Parameters["@Im_no"].Value = sIm_no;
        com.Parameters.Add("@cur_payMoney", SqlDbType.Decimal, 28);
        com.Parameters["@cur_payMoney"].Value = dCurPay_money;
        com.Parameters.Add("@state", SqlDbType.Int, 1);
        com.Parameters["@state"].Value = iState;

        try
        {
            con.Open();
            com.ExecuteNonQuery();
            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    ///////////////////////////////////////////////////////采购退款部分（共用一个CS）///////////////////////////////////////////////////////////

    /// <summary>
    /// 新增采购退款单明细记录(自动带入明细)
    /// </summary>CREATE BY KIDY ON 20070109
    /// <returns>返回操作是否成功</returns>
    public Boolean AddStockBackFundChild(string sType, string sPay_no, DateTime dPay_date, string sVm_no, DateTime dVm_Date,
        string sRemark, int ActionType)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AddStockBackFundChild", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@type", SqlDbType.NVarChar, 2);
        com.Parameters["@type"].Value = sType;
        com.Parameters.Add("@pay_no", SqlDbType.NVarChar, 30);
        com.Parameters["@pay_no"].Value = sPay_no;
        com.Parameters.Add("@pay_date", SqlDbType.DateTime, 8);
        com.Parameters["@pay_date"].Value = dPay_date;
        com.Parameters.Add("@back_no", SqlDbType.NVarChar, 30);
        com.Parameters["@back_no"].Value = sVm_no;
        com.Parameters.Add("@back_Date", SqlDbType.DateTime, 8);
        com.Parameters["@back_Date"].Value = dVm_Date;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = sRemark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();
            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 修改采购退款单明细记录
    /// </summary>CREATE BY KIDY ON 20070109
    /// <returns>返回操作是否成功</returns>
    public Boolean UpdateStockBackFundChild(string sType, string sPay_no, string sVm_no, string sIm_no,string sLot_no,
        decimal dBack_money, decimal dCurBack_money, decimal dRate, string sRemark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpdateStockBackFundChild", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@type", SqlDbType.NVarChar, 2);
        com.Parameters["@type"].Value = sType;
        com.Parameters.Add("@pay_no", SqlDbType.NVarChar, 30);
        com.Parameters["@pay_no"].Value = sPay_no;
        com.Parameters.Add("@vm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@vm_no"].Value = sVm_no;
        com.Parameters.Add("@Im_no", SqlDbType.NVarChar, 30);
        com.Parameters["@Im_no"].Value = sIm_no;
        com.Parameters.Add("@lot_no", SqlDbType.NVarChar, 30);
        com.Parameters["@lot_no"].Value = sLot_no;
        com.Parameters.Add("@Back_money", SqlDbType.Decimal, 28);
        com.Parameters["@Back_money"].Value = dBack_money;
        com.Parameters.Add("@CurBack_money", SqlDbType.Decimal, 28);
        com.Parameters["@CurBack_money"].Value = dCurBack_money;
        com.Parameters.Add("@rate", SqlDbType.Decimal, 28);
        com.Parameters["@rate"].Value = dRate;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = sRemark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();
            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 删除采购退款单主表或者明细表记录 
    /// </summary>
    /// <param>CREATE BY KIDY ON 20070110</param>
    /// <param name="Is_Visible"></param>
    /// <returns>返回删除是否成功</returns>
    public Boolean DeleteStockBackFund(string sType, string sPay_no, string sVm_no, string sIm_no,string sLot_no, 
        decimal dCurPay_money,decimal dCur_Rate,int iState)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_DeleteStockBackFund", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@type", SqlDbType.NVarChar, 30);
        com.Parameters["@type"].Value = sType;
        com.Parameters.Add("@pay_no", SqlDbType.NVarChar, 30);
        com.Parameters["@pay_no"].Value = sPay_no;
        com.Parameters.Add("@vm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@vm_no"].Value = sVm_no;
        com.Parameters.Add("@Im_no", SqlDbType.NVarChar, 30);
        com.Parameters["@Im_no"].Value = sIm_no;
        com.Parameters.Add("@lot_no", SqlDbType.NVarChar, 30);
        com.Parameters["@lot_no"].Value = sLot_no;
        com.Parameters.Add("@cur_payMoney", SqlDbType.Decimal, 28);
        com.Parameters["@cur_payMoney"].Value = dCurPay_money;
        com.Parameters.Add("@rate", SqlDbType.Decimal, 28);
        com.Parameters["@rate"].Value = dCur_Rate;
        com.Parameters.Add("@state", SqlDbType.Int, 1);
        com.Parameters["@state"].Value = iState;

        try
        {
            con.Open();
            com.ExecuteNonQuery();
            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 检查采购退款单是否存在明细，删除用
    /// </summary>CREATE BY KIDY ON 20070110
    /// <returns>返回操作是否成功</returns>
    public Boolean ChkChildbyStockFund(string sType, string sPay_no)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_ChkChildbyStockFund", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@type", SqlDbType.NVarChar, 2);
        com.Parameters["@type"].Value = sType;
        com.Parameters.Add("@bod_no", SqlDbType.NVarChar, 30);
        com.Parameters["@bod_no"].Value = sPay_no;

        try
        {
            con.Open();
            com.ExecuteNonQuery();
            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 根据查询状态得到采购付款单据信息
    /// </summary>CREATE BY KIDY ON 20070122
    /// <param name="Type">类型</param>
    /// <returns>返回主表信息数据集</returns>
    public DataSet GetOrderInformationByStatu(string sCurUserId, string sTable, int iType, int iQueryType)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetOrderByStockPayFund", con);
        dat.SelectCommand.CommandType = CommandType.StoredProcedure;

        dat.SelectCommand.Parameters.Add("@u_log_id", SqlDbType.VarChar, 30);
        dat.SelectCommand.Parameters["@u_log_id"].Value = sCurUserId;
        dat.SelectCommand.Parameters.Add("@table", SqlDbType.VarChar, 30);
        dat.SelectCommand.Parameters["@table"].Value = sTable;
        dat.SelectCommand.Parameters.Add("@type", SqlDbType.Int, 2);
        dat.SelectCommand.Parameters["@type"].Value = iType;
        dat.SelectCommand.Parameters.Add("@queryType", SqlDbType.Int, 2);
        dat.SelectCommand.Parameters["@queryType"].Value = iQueryType;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

}
